package com.ruoyi.excelFileIoHandler;

/**
 * @author jitwxs
 * @date 2023年12月10日 17:33
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ruoyi.AesUtil.AESUtil;
import com.ruoyi.mqttClient.AutoSendMsgMqttCallback;
import com.ruoyi.mqttClient.SubPubResult;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service("readExcel")
public class ReadExcel {
    private static final Logger logger = LoggerFactory.getLogger(ReadExcel.class);
    private String sendMsg = "";
    private final static String topic = "action/16";
    public SubPubResult subPubResult;
    @Autowired
    private AutoSendMsgMqttCallback autoSendMsgMqttCallback;
    @Autowired
    private AESUtil aesUtil;

    /**
     * 读取2003Excel
     *
     * @param filepath 文件路径
     * @param filename 文件名，包括扩展名
     * @param startrow 开始行号，索引从0开始
     * @param startcol 开始列号，索引从0开始
     * @param sheetnum 工作簿，索引从0开始
     * @return
     */
    public void readExcel2003(String filepath, String filename, int startrow, int startcol, int sheetnum) {
        try {
            File target = new File(filepath, filename);
            FileInputStream fis = new FileInputStream(target);
            HSSFWorkbook wb = new HSSFWorkbook(fis);
            fis.close();
            // sheet 从0开始
            HSSFSheet sheet = wb.getSheetAt(sheetnum);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;

            HSSFRow rowTitle = sheet.getRow(0);
            // 标题行的最后一个单元格位置
            int cellTitleNum = rowTitle.getLastCellNum();
            String[] title = new String[cellTitleNum];
            for (int i = startcol; i < cellTitleNum; i++) {
                HSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    title[i] = cell.getStringCellValue();
                } else {
                    title[i] = "";
                }
            }

            // 行循环开始
            for (int i = startrow + 1; i < rowNum; i++) {
                Map<String, String> varpd = new HashMap<>();
                // 行
                HSSFRow row = sheet.getRow(i);
                // 列循环开始
                for (int j = startcol; j < cellTitleNum; j++) {

                    HSSFCell cell = row.getCell(Short.parseShort(j + ""));
                    String cellValue = "";
                    if (cell != null) {
                        // 把类型先设置为字符串类型
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.getStringCellValue();
                        logger.info("获取表格内容：", cellValue);
                        if (j == 0) {
                            sendMsg = "\"clients\":[\"" + cellValue + "\"],\"todo\":\"setConfig\",\"centerConfig\":{\"ip\":\"http://118.113.105.29:8001/\",\"license\":\"";
                        }
                        if (j == 1) {
                            sendMsg = sendMsg + cellValue + "\",\"owner\":\"SC\",\"type\":\"TER\",\"groupId\":\"\",\"clientId\":\"\",\"uname\":\"\",\"pwd\":\"\",\"scope\":\"EEW,EQR,ACK\",\"mqtthost\":\"\",\"topic\":\"\",\"eqrTopic\":\"\",\"answerTopic\":\"\",\"port\":\"\",\"ftpEnable\":\"true\",\"ftpHost\":\"\",\"ftpPort\":\"21\",\"ftpName\":\"\",\"ftpPsw\":\"\",\"contentType\":\"\",\"certType\":\"0\",\"authMode\":\"0\",\"path1\":\"\",\"path2\":\"\",\"path3\":\"\",\"path4\":\"\",\"enableVerify\":\"false\",\"caPath\":\"\",\"caType\":\"0\"}}";
                            //sendMsg = aesUtil.encryptNoPadding(sendMsg);
                            subPubResult = autoSendMsgMqttCallback.publish(topic, 2, sendMsg);
                            sendMsg="";
                            logger.info("subPubResultStatus:", subPubResult.getStatus(), "subPubResultOk", subPubResult.isOk());
                            Thread.sleep(30000);
                        }

                        //sendMsg = "{\"clients\":[\"" + cellValue + "\"],\"todo\":\"terminal\",\"cmd\":\"restart\",\"guardCmd\":true}";
                        //sendMsg = aesUtil.encryptNoPadding(sendMsg);
                        //subPubResult = autoSendMsgMqttCallback.publish(topic, 2, sendMsg);

                    }
                }
            }
            wb.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    /**
     * 读取2007Excel
     *
     * @param filepath 文件路径
     * @param filename 文件名，包括扩展名
     * @param startrow 开始行号，索引从0开始
     * @param startcol 开始列号，索引从0开始
     * @param sheetnum 工作簿，索引从0开始
     * @return
     */
    public void readExcel2007(String filepath, String filename, int startrow, int startcol, int sheetnum) {

        try {
            File target = new File(filepath, filename);
            InputStream ins = new FileInputStream(target);
            XSSFWorkbook wb = new XSSFWorkbook(ins);
            ins.close();
            // 得到Excel工作表对象
            XSSFSheet sheet = wb.getSheetAt(sheetnum);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;

            XSSFRow rowTitle = sheet.getRow(0);
            int cellTitleNum = rowTitle.getLastCellNum();
            String[] title = new String[cellTitleNum];
            for (int i = startcol; i < cellTitleNum; i++) {
                XSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));
                if (cell != null) {
                    // 把类型先设置为字符串类型
                    cell.setCellType(CellType.STRING);
                    title[i] = cell.getStringCellValue();
                } else {
                    title[i] = "";
                }
            }

            // 行循环开始
            for (int i = startrow + 1; i < rowNum; i++) {
                Map<String, String> varpd = new HashMap<>();
                // 得到Excel工作表的行
                XSSFRow row = sheet.getRow(i);
                // 列循环开始
                for (int j = startcol; j < cellTitleNum; j++) {
                    // 得到Excel工作表指定行的单元格
                    XSSFCell cell = row.getCell(j);
                    String cellValue = "";
                    if (cell != null) {
                        // 把类型先设置为字符串类型
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.getStringCellValue();
                        logger.info("获取表格内容：", cellValue);
                        sendMsg = "{\"clients\":[\"" + cellValue + "],\"todo\":\"terminal\",\"cmd\":\"restart\",\"guardCmd\":true}";
                        subPubResult = autoSendMsgMqttCallback.publish(topic, 2, sendMsg);
                        logger.info("subPubResultStatus:", subPubResult.getStatus(), "subPubResultOk", subPubResult.isOk());
                        Thread.sleep(30000);
                    }
                    varpd.put(title[j], cellValue);
                }
            }
            wb.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public void readExcelTest(String filepath, String filename, int startrow, int startcol, int sheetnum) {

        String suffix = filename.substring(filename.lastIndexOf(".") + 1);
        if ("xls".equals(suffix)) {
            readExcel2003(filepath, filename, startrow, startcol, sheetnum);
        } else if ("xlsx".equals(suffix)) {
            readExcel2007(filepath, filename, startrow, startcol, sheetnum);
        } else {
            System.out.println("Only excel files with XLS or XLSX suffixes are allowed to be read!");
        }


    }


}
